﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for News
/// </summary>
/// 
namespace BusLogic
{ 
    public class News
    {
        public static DataSet get_ALL_NEWS()
        {
            DataSet retData = new DataSet();
            String connString = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;

            using (SqlConnection Conn = new SqlConnection(connString))
            {
                try
                {
                    String strCommandText;
                    SqlCommand objCommand;

                    strCommandText = @"SELECT NEWS.ID AS 'ID', NEWS.TITLE, CATEGORY.NAME AS 'CATEGORY', NEWS.SORTED, NEWS.RECORD_STATUS
                                    FROM NEWS INNER JOIN CATEGORY
                                    ON NEWS.CATEID = CATEGORY.ID
                                    ORDER BY CATEGORY ASC, SORTED ASC";

                    objCommand = new SqlCommand(strCommandText, Conn);
                    objCommand.CommandType = CommandType.Text;

                    SqlDataAdapter adap = new SqlDataAdapter(objCommand);
                    Conn.Open();
                    adap.Fill(retData, "Table");
                    Conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (Conn.State == ConnectionState.Open)
                        Conn.Close();
                }
            }
            return retData;
        }

        public static DataSet get_NEWS(int iID)
        {
            DataSet retData = new DataSet();
            String connString = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;

            using (SqlConnection Conn = new SqlConnection(connString))
            {
                try
                {
                    String strCommandText;
                    SqlCommand objCommand;

                    strCommandText = "SELECT * FROM NEWS WHERE ID = " + iID.ToString();

                    objCommand = new SqlCommand(strCommandText, Conn);
                    objCommand.CommandType = CommandType.Text;

                    SqlDataAdapter adap = new SqlDataAdapter(objCommand);
                    Conn.Open();
                    adap.Fill(retData, "Table");
                    Conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (Conn.State == ConnectionState.Open)
                        Conn.Close();
                }
            }
            return retData;
        }

        public static void remove_NEWS(int iID)
        {
            string connStr = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;
            SqlConnection connection = new SqlConnection(connStr);
            try
            {
                string strCommandName;
                SqlCommand objCommand;
                SqlParameter objParam;

                strCommandName = "DELETE FROM NEWS WHERE ID=@ID";
                objCommand = new SqlCommand(strCommandName, connection);
                objCommand.CommandType = CommandType.Text;
                //Set the Parameters

                objParam = objCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                objParam.Direction = ParameterDirection.Input;
                objParam.Value = iID;

                connection.Open();
                objCommand.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        public static void add_NEWS(int iCateId ,string sTitle, string sImage, string sSummary,  string sContent, string sAuthors, DateTime dCreateDate, int iSorted, bool bRecordStatus)
        {
            string sConnect = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ToString();
            using (SqlConnection conn = new SqlConnection(sConnect))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdAddProduct = new SqlCommand("sp_ADD_NEWS", conn);
                    cmdAddProduct.CommandType = CommandType.StoredProcedure;

                    SqlParameter sqlPara;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@CATEID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iCateId;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@TITLE", SqlDbType.NVarChar, 300));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sTitle;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@IMAGE", SqlDbType.NVarChar, 300));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sImage;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@SUMMARY", SqlDbType.NText));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sSummary;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@CONTENT", SqlDbType.NText));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sContent;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@AUTHORS", SqlDbType.NVarChar, 50));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sAuthors;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@CREATE_DATE", SqlDbType.DateTime));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = dCreateDate;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@SORTED", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iSorted;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@RECORD_STATUS", SqlDbType.Bit));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = bRecordStatus;

                    cmdAddProduct.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
        }

        public static void update_NEWS(int iID, int iCateId, string sTitle, string sImage, string sSummary, string sContent, string sAuthors, DateTime dCreateDate, int iSorted, bool bRecordStatus)
        {
            string sConnect = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ToString();
            using (SqlConnection conn = new SqlConnection(sConnect))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdAddProduct = new SqlCommand("sp_UPDATE_NEWS", conn);
                    cmdAddProduct.CommandType = CommandType.StoredProcedure;

                    SqlParameter sqlPara;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iID;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@CATEID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iCateId;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@TITLE", SqlDbType.NVarChar, 300));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sTitle;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@IMAGE", SqlDbType.NVarChar, 300));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sImage;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@SUMMARY", SqlDbType.NText));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sSummary;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@CONTENT", SqlDbType.NText));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sContent;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@AUTHORS", SqlDbType.NVarChar, 50));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sAuthors;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@CREATE_DATE", SqlDbType.DateTime));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = dCreateDate;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@SORTED", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iSorted;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@RECORD_STATUS", SqlDbType.Bit));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = bRecordStatus;

                    cmdAddProduct.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
        }


        public static void update_LINK_IMAGE_NEWS(int iID, string sImage)
        {
            string sConnect = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ToString();
            using (SqlConnection conn = new SqlConnection(sConnect))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdAddProduct = new SqlCommand("sp_UPDATE_IMAGE_NEWS", conn);
                    cmdAddProduct.CommandType = CommandType.StoredProcedure;

                    SqlParameter sqlPara;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iID;
                    

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@IMAGE", SqlDbType.NVarChar, 300));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sImage;                    

                    cmdAddProduct.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
        }


        public static DataSet search_NEWS(string sNewsName, int iCatId)
        {
            DataSet retData = new DataSet();
            String connString = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;

            using (SqlConnection Conn = new SqlConnection(connString))
            {
                try
                {
                    String strCommandText;
                    SqlCommand objCommand;
                    bool isAddWhere = false;

                    strCommandText = @"SELECT NEWS.ID AS 'ID', NEWS.TITLE, CATEGORY.NAME AS 'CATEGORY', NEWS.SORTED, NEWS.RECORD_STATUS
                                    FROM NEWS INNER JOIN CATEGORY
                                    ON NEWS.CATEID = CATEGORY.ID ";

                    if (!String.IsNullOrEmpty(sNewsName))
                    {
                        if (!isAddWhere)
                        {
                            strCommandText += "WHERE TITLE LIKE N'%" + sNewsName + "%'";
                            isAddWhere = true;
                        }
                        else
                        {
                            strCommandText += "AND TITLE LIKE N'%" + sNewsName + "%'";
                        }
                    }

                    if (iCatId > 0)
                    {
                        if (!isAddWhere)
                        {
                            strCommandText += "WHERE CATEID = " + iCatId.ToString();
                            isAddWhere = true;
                        }
                        else
                        {
                            strCommandText += "AND CATEID = " + iCatId.ToString();
                        }
                    }

                    strCommandText += " ORDER BY SORTED ";

                    objCommand = new SqlCommand(strCommandText, Conn);
                    objCommand.CommandType = CommandType.Text;

                    SqlDataAdapter adap = new SqlDataAdapter(objCommand);
                    Conn.Open();
                    adap.Fill(retData, "Table");
                    Conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (Conn.State == ConnectionState.Open)
                        Conn.Close();
                }
            }
            return retData;
        }
    }
}


